update sms_income_settlement_line t set t.is_fee_deducation='Y' where t.is_fee_deducation='N';


DELETE SMS.SMS_INCOME_SETTLEMENT_LINE ISL
 WHERE ISL.IS_FEE_DEDUCATION <> 'Y'
   AND NOT EXISTS (SELECT 1
          FROM SMS.SMS_INCOME_SETTLEMENT SIS
         WHERE SIS.ID = ISL.INCOME_SETTLEMENT_ID);

TRUNCATE  TABLE SMS_CASH_ACCT_LINE;

DECLARE
  V_CUR_SEQ NUMBER;
  V_NEW_SEQ NUMBER;
  V_NEW_SEQ_TMP NUMBER;
  V_SEQ NUMBER;
  V_SQL VARCHAR2(128);
BEGIN
  SELECT SMS.SMS_COST_SETTLEMENT_LINE_SEQ.NEXTVAL INTO V_CUR_SEQ FROM DUAL;
  SELECT (max(id) + 1) INTO V_NEW_SEQ from SMS.SMS_COST_SETTLEMENT_LINE;

  select (V_NEW_SEQ - V_CUR_SEQ) INTO V_SEQ FROM DUAL;
  DBMS_OUTPUT.put_line(V_SEQ);
  if V_SEQ > 0 then
    V_SQL := 'ALTER SEQUENCE SMS.SMS_COST_SETTLEMENT_LINE_SEQ INCREMENT BY ' || ABS(V_SEQ);
    DBMS_OUTPUT.put_line(V_SQL);
    execute immediate V_SQL;
    SELECT SMS.SMS_COST_SETTLEMENT_LINE_SEQ.NEXTVAL INTO V_NEW_SEQ_TMP FROM DUAL;
    execute IMMEDIATE 'ALTER SEQUENCE SMS.SMS_COST_SETTLEMENT_LINE_SEQ INCREMENT BY 1';
  END IF;

END;

/

DECLARE
  V_CUR_SEQ NUMBER;
  V_NEW_SEQ NUMBER;
  V_NEW_SEQ_TMP NUMBER;
  V_SEQ NUMBER;
  V_SQL VARCHAR2(128);
BEGIN
  SELECT SMS.SMS_INCOME_SETTLEMENT_LINE_SEQ.NEXTVAL INTO V_CUR_SEQ FROM DUAL;
  SELECT (max(id) + 1) INTO V_NEW_SEQ from SMS.SMS_INCOME_SETTLEMENT_LINE;

  select (V_NEW_SEQ - V_CUR_SEQ) INTO V_SEQ FROM DUAL;
  DBMS_OUTPUT.put_line(V_SEQ);
  if V_SEQ > 0 then
    V_SQL := 'ALTER SEQUENCE SMS.SMS_INCOME_SETTLEMENT_LINE_SEQ INCREMENT BY ' || ABS(V_SEQ);
    DBMS_OUTPUT.put_line(V_SQL);
    execute immediate V_SQL;
    SELECT SMS.SMS_INCOME_SETTLEMENT_LINE_SEQ.NEXTVAL INTO V_NEW_SEQ_TMP FROM DUAL;
    execute IMMEDIATE 'ALTER SEQUENCE SMS.SMS_INCOME_SETTLEMENT_LINE_SEQ INCREMENT BY 1';
  END IF;

END;

/

DECLARE
cursor c_seq is
select SEQUENCE_NAME||','||last_number from user_sequences
order by SEQUENCE_NAME;
p_sequenceName varchar2(50);
p_sequenceName1 varchar2(50);
p_current_seq_value  varchar2(50);
p_seqNextValue varchar2(50);
p_diff_value number;
a varchar2(10);
BEGIN
  open c_seq;
 LOOP
    fetch c_seq into p_sequenceName;
    exit when c_seq%notfound;
    p_sequenceName1:=SUBSTR(p_sequenceName,0,INSTR(p_sequenceName,',')-1);
    p_current_seq_value:=SUBSTR(p_sequenceName,INSTR(p_sequenceName,',')+1);
    p_seqNextValue:=to_number(SUBSTR(p_sequenceName,INSTR(p_sequenceName,',')+1))+1000000;
    p_diff_value:=to_number(p_seqNextValue)-to_number(p_current_seq_value);
       execute immediate'alter sequence '|| p_sequenceName1|| ' increment by '|| abs(p_diff_value)  ||' nocache';
       execute immediate 'select '||p_sequenceName1||'.nextval from dual' into a;
       execute immediate 'alter sequence '|| p_sequenceName1|| ' increment by 1 cache 20';
  END LOOP;
   exception when others then
    dbms_output.put_line('sqlerrm='||SQLERRM);
END;

/

exit